USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetTaskDetails')
	BEGIN
		DROP  Procedure  dbo.GetTaskDetails
	END
GO

CREATE Procedure [dbo].[GetTaskDetails]
	@PKID		nvarchar(100) = NULL
,	@OWNERID	varchar(10) = ''
AS
BEGIN

	declare 
		@sql			varchar(max)
	,	@accounts		varchar(max)
	,	@contacts		varchar(max)
	,	@allUsers		varchar(max)
	,	@taskstatus	varchar(max)
	,	@relations		varchar(max)

	SELECT	@sql = ''
		,	@accounts		= dbo.GetAccountsXml(@OWNERID)
		,	@contacts		= dbo.GetContactsXml('')
		,	@allUsers		= dbo.GetAllUsersXml()
		,	@taskstatus	= dbo.GetStatusXml('Task')
		,	@relations		= dbo.GetRelationsXML(@PKID)

	;WITH A AS (
		SELECT 
			at.ActivityTaskID		AS TaskID
		,	at.Description			AS TaskName
		,	at.ActivityTaskGuid		AS TaskGUID	
		,	at.Status				as TaskStatusID
		,	at.StartDate			as TaskStartDate
		,	at.EndDate				as TaskEndDate
		FROM TR_ACTIVITYTASK as at WITH (NOLOCK)
		WHERE
			CAST(at.ActivityTaskGUID AS VARCHAR(40)) = ISNULL(@PKID, '')
		UNION
		SELECT TOP 1
			'0'						AS TaskID
		,	''						AS TaskName
		,	NEWID()					AS TaskGUID	
		,	'0'						as TaskStatusID
		,	GetDate()				as TaskStartDate
		,	GETDATE()+1				as TaskEndDate
		WHERE
			ISNULL(@PKID, '') = ''
	), B AS (
		SELECT TOP 1
			n.NoteID
		,	n.NoteText
		,	n.SourceGUID
		FROM TR_NOTE as n with (NOLOCK)
		INNER JOIN A as a with (NOLOCK)
			on a.TaskGUID = n.SourceGUID
	), FinalXML AS (
		SELECT 1 AS ID, N'<DATA>' AS Details 
		UNION
		SELECT 2 AS ID, N'<RESPONSE><RESULT>S</RESULT><MESSAGE></MESSAGE></RESPONSE>'
		UNION
		SELECT 2.1 as ID, @accounts as Details
		UNION
		SELECT 2.2 as ID, @contacts as Details
		UNION
		SELECT 2.5 as ID, @allUsers as Details
		UNION
		SELECT 2.7 as ID, @taskstatus as Details
		UNION
		SELECT 2.8 as ID, @relations as Details
		UNION
		SELECT
			3 AS ID, 
			'<PKID><![CDATA[' + ISNULL(CAST(A.TASKGUID AS VARCHAR(40)), '') + ']]></PKID>'
		+	'<TASKID><![CDATA['+ ISNULL( CAST(A.TASKID AS VARCHAR),'') +']]></TASKID>'
		+	'<TASKGUID><![CDATA[' + ISNULL(CAST(A.TASKGUID AS VARCHAR(40)), '') + ']]></TASKGUID>'
		+	'<TASKNAME><![CDATA['+ ISNULL(A.TASKName,'') +']]></TASKNAME>'
		+	'<TASKSTATUSID><![CDATA['+ ISNULL( CAST(A.TASKSTATUSID AS VARCHAR),'') +']]></TASKSTATUSID>'
		+	'<STARTDATE><![CDATA['+ CONVERT(VARCHAR(10), ISNULL(TASKStartDate, GETDATE()), 101)+']]></STARTDATE>'
		+	'<ENDDATE><![CDATA['+CONVERT(VARCHAR(10), ISNULL(TASKEndDate, GETDATE()), 101)+']]></ENDDATE>'
		+	'<NOTEID><![CDATA[' + CAST(ISNULL(n.NoteID,0) AS VARCHAR) + ']]></NOTEID>'
		+	'<NOTES><![CDATA[' + ISNULL(n.NOTETEXT,'') + ']]></NOTES>'
					AS Details
		FROM A as A
		LEFT OUTER JOIN B as n
			on n.SourceGUID = A.TaskGUID
		UNION
		SELECT 4 AS ID, '</DATA>' AS Details 
	)

	SELECT @sql = @sql + Details
	FROM FinalXML
	Order by ID

	select @sql	AS Detail
END
GO

-- GetTaskDetails